package com.daxia.bumall.dao;

import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Repository;

import com.daxia.bumall.common.Logs;
import com.daxia.bumall.daobase.GenericDAOHibernate;
import com.daxia.bumall.dto.OrderStatisticsDTO;

@Repository
public class OrderStatisticsDAO extends GenericDAOHibernate<OrderStatisticsDTO> {
	
	/**
	 * 根据月份统计订单
	 * @param dto
	 * @return
	 */
	public List<OrderStatisticsDTO> findForMonth(OrderStatisticsDTO dto) {
		StringBuffer buff = new StringBuffer();
		List<Object> paras = new ArrayList<Object>();
		buff.append("SELECT DATE_FORMAT(createTime, '%Y-%m') `month`, ");
		buff.append("SUM(case when payStatus=1 then 1 else 0 end) quantity, ");
		buff.append("SUM(case when payStatus=1 then amount else 0 end) amount, ");
		buff.append("COUNT(DATE_FORMAT(createTime, '%Y-%m')) allQuantity, ");
		buff.append("SUM(amount) allAmount ");
		buff.append(" FROM `order`");
		buff.append(" where 1 = 1 ");
//		buff.append(" WHERE payStatus=1 ");
		if (dto.getCity() != null) {
			buff.append(" and city_id = ? and user_id != ?");
			paras.add(dto.getCity().getId());
			paras.add(dto.getUserId());
		} else if (dto.getUserId() != null) {
			buff.append(" and user_id = ? ");
			paras.add(dto.getUserId());
		}
		buff.append(" GROUP BY DATE_FORMAT(createTime, '%Y-%m') ");
		buff.append(" ORDER BY DATE_FORMAT(createTime, '%Y-%m') desc ");
		if (null != dto.getStart() && null != dto.getEnd()) {
			buff.append(" limit ?,? ");
			paras.add(dto.getStart());
			paras.add(dto.getEnd());
		}
		Logs.COMMON.debug(buff.toString());
		return super.findBySQL(buff.toString(), paras.toArray(), OrderStatisticsDTO.class);
	}
	
	/**
	 * 根据天统计订单
	 * @param dto
	 * @return
	 */
	public List<OrderStatisticsDTO> findForDay(OrderStatisticsDTO dto) {
		StringBuffer buff = new StringBuffer();
		List<Object> paras = new ArrayList<Object>();
		buff.append("SELECT DATE_FORMAT(createTime, '%Y-%m-%d') `month`,");
		buff.append("SUM(case when payStatus=1 then 1 else 0 end) quantity,");
		buff.append("SUM(case when payStatus=1 then amount else 0 end) amount, ");
		buff.append("COUNT(DATE_FORMAT(createTime, '%Y-%m-%d')) allQuantity, ");
		buff.append("SUM(amount) allAmount ");
		buff.append("FROM `order` WHERE 1=1 ");
		if (dto.getCity() != null) {
			buff.append(" and city_id = ? and user_id != ?");
			paras.add(dto.getCity().getId());
			paras.add(dto.getUserId());
		} else if (dto.getUserId() != null) {
			buff.append(" and user_id = ? ");
			paras.add(dto.getUserId());
		}
		buff.append(" GROUP BY DATE_FORMAT(createTime, '%Y-%m-%d') ");
		buff.append(" ORDER BY DATE_FORMAT(createTime, '%Y-%m-%d') desc ");
		if (null != dto.getStart() && null != dto.getEnd()) {
			buff.append(" limit ?,? ");
			paras.add(dto.getStart());
			paras.add(dto.getEnd());
		}
		return super.findBySQL(buff.toString(), paras.toArray(), OrderStatisticsDTO.class);
	}
	/**
	 * 根据年度统计订单
	 * @param dto
	 * @return
	 */
	public List<OrderStatisticsDTO> findForYear(OrderStatisticsDTO dto) {
		StringBuffer buff = new StringBuffer();
		List<Object> paras = new ArrayList<Object>();
		buff.append("SELECT DATE_FORMAT(createTime, '%Y') `month`,");
		buff.append("COUNT(DATE_FORMAT(createTime, '%Y')) quantity,");
		buff.append("SUM(amount) amount FROM `order`");
		buff.append(" WHERE payStatus=1 ");
		if (dto.getCity() != null) {
			buff.append(" and city_id = ? and user_id != ?");
			paras.add(dto.getCity().getId());
			paras.add(dto.getUserId());
		} else if (dto.getUserId() != null) {
			buff.append(" and user_id = ? ");
			paras.add(dto.getUserId());
		}
		buff.append(" GROUP BY DATE_FORMAT(createTime, '%Y') ");
		buff.append(" ORDER BY DATE_FORMAT(createTime, '%Y') desc ");
		if (null != dto.getStart() && null != dto.getEnd()) {
			buff.append(" limit ?,? ");
			paras.add(dto.getStart());
			paras.add(dto.getEnd());
		}
		return super.findBySQL(buff.toString(), paras.toArray(), OrderStatisticsDTO.class);
	}
	
	/**
	 * 根据季度统计订单
	 * @param dto
	 * @return
	 */
	public List<OrderStatisticsDTO> findForQuarter(OrderStatisticsDTO dto) {
		StringBuffer buff = new StringBuffer();
		List<Object> paras = new ArrayList<Object>();
		buff.append("SELECT CONCAT(DATE_FORMAT(createTime, '%Y'),FLOOR((DATE_FORMAT(createTime, '%m')+2)/3)) month,");
		buff.append("COUNT(CONCAT(DATE_FORMAT(createTime, '%Y'),FLOOR((DATE_FORMAT(createTime, '%m')+2)/3))) quantity,");
		buff.append("SUM(amount) amount FROM `order`");
		buff.append(" WHERE payStatus=1 ");
		if (dto.getCity() != null) {
			buff.append(" and city_id = ? and user_id != ?");
			paras.add(dto.getCity().getId());
			paras.add(dto.getUserId());
		} else if (dto.getUserId() != null) {
			buff.append(" and user_id = ? ");
			paras.add(dto.getUserId());
		}
		buff.append(" GROUP BY CONCAT(DATE_FORMAT(createTime, '%Y'),FLOOR((DATE_FORMAT(createTime, '%m')+2)/3)) ");
		buff.append(" ORDER BY CONCAT(DATE_FORMAT(createTime, '%Y'),FLOOR((DATE_FORMAT(createTime, '%m')+2)/3)) desc ");
		if (null != dto.getStart() && null != dto.getEnd()) {
			buff.append(" limit ?,? ");
			paras.add(dto.getStart());
			paras.add(dto.getEnd());
		}
		return super.findBySQL(buff.toString(), paras.toArray(), OrderStatisticsDTO.class);
	}
	
	public List<OrderStatisticsDTO> findSubAccountForMonth(OrderStatisticsDTO dto) {
		return null;
	}
	
	/**
	 * 查找所有设计师个数
	 * @param dto
	 * @return
	 */
	/*public List<OrderStatisticsDTO> findAllDesigner(OrderStatisticsDTO dto) {
		StringBuffer buff = new StringBuffer();
		List<Object> paras = new ArrayList<Object>();
		buff.append("SELECT COUNT(*) FROM designer d,`user` u WHERE u.id = d.user_id");
		if (dto.getCity() != null) {
			buff.append(" and d.city_id = ? ");
			paras.add(dto.getCity().getId());
		}
		return super.findBySQL(buff.toString(), paras.toArray(), OrderStatisticsDTO.class);
	}*/
	
	/**
	 * 查找所有装修公司个数
	 * @param dto
	 * @return
	 */
	public List<OrderStatisticsDTO> findAllCompany(OrderStatisticsDTO dto) {
		StringBuffer buff = new StringBuffer();
		List<Object> paras = new ArrayList<Object>();
		buff.append("SELECT COUNT(*) quantity FROM company c ,`user` u WHERE u.id = c.user_id");
		if (dto.getCity() != null) {
			buff.append(" and c.city_id = ? ");
			paras.add(dto.getCity().getId());
		}
		return super.findBySQL(buff.toString(), paras.toArray(), OrderStatisticsDTO.class);
	}
	
	/**
	 * 根据月份查找设计师数量
	 * @return
	 */
	/*public List<OrderStatisticsDTO> findDesignerCountForMonth(OrderStatisticsDTO dto) {
		StringBuffer buff = new StringBuffer();
		List<Object> paras = new ArrayList<Object>();
		buff.append("SELECT IFNULL(COUNT(*),0) quantity,DATE_FORMAT(createTime, '%Y-%m') `month`");
		buff.append(" FROM designer d,`user` u WHERE u.id = d.user_id ");
		if (dto.getCity() != null) {
			buff.append(" and d.city_id = ? ");
			paras.add(dto.getCity().getId());
		}
		buff.append(" GROUP BY DATE_FORMAT(createTime, '%Y-%m') ORDER BY DATE_FORMAT(createTime, '%Y-%m') DESC");
		if (null != dto.getStart() && null != dto.getEnd()) {
			buff.append(" limit ?,? ");
			paras.add(dto.getStart());
			paras.add(dto.getEnd());
		}
		return super.findBySQL(buff.toString(), paras.toArray(), OrderStatisticsDTO.class);
	}*/
	
	/**
	 * 根据月份查找公司数量
	 * @param dto
	 * @return
	 */
	public List<OrderStatisticsDTO> findCompanyCountForMonth(OrderStatisticsDTO dto) {
		StringBuffer buff = new StringBuffer();
		List<Object> paras = new ArrayList<Object>();
		buff.append("SELECT IFNULL(COUNT(*),0) quantity,DATE_FORMAT(createTime, '%Y-%m') `month`");
		buff.append(" FROM company c,`user` u WHERE u.id = c.user_id ");
		if (dto.getCity() != null) {
			buff.append(" and c.city_id = ? ");
			paras.add(dto.getCity().getId());
		}
		buff.append(" GROUP BY DATE_FORMAT(createTime, '%Y-%m') ORDER BY DATE_FORMAT(createTime, '%Y-%m') DESC");
		if (null != dto.getStart() && null != dto.getEnd()) {
			buff.append(" limit ?,? ");
			paras.add(dto.getStart());
			paras.add(dto.getEnd());
		}
		return super.findBySQL(buff.toString(), paras.toArray(), OrderStatisticsDTO.class);
	}
}
